package com.tencent.albert.weixin.driver.dao;

import com.tencent.albert.weixin.driver.Constants;
import com.tencent.albert.weixin.driver.bean.ReceivedMsgInfo;
import com.tencent.albert.weixin.driver.bean.SendMsgInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * Created by albertzhu on 15/2/13.
 */
public class MsgDao extends BaseDao{

    private static Logger logger = LoggerFactory.getLogger(MsgDao.class);

    SimpleDateFormat formatter = new SimpleDateFormat ("YYYY_MM");

    long lastCheckCurrrentTabelName;
    private String currentTableNameTail = null;

    public MsgDao() {
        lastCheckCurrrentTabelName = 0;
    }

    public String getCurrentTableName() {
        long now = System.currentTimeMillis();
        if (null == currentTableNameTail || now - lastCheckCurrrentTabelName > 60 * 1000 ) {
            Calendar c = Calendar.getInstance();
            currentTableNameTail = formatter.format(c.getTime());
            lastCheckCurrrentTabelName = now;
        }
        return currentTableNameTail;
    }


    public void insertMsgLog(List<ReceivedMsgInfo> infos) {

        Connection con = getConn();
        PreparedStatement pstmt = null;
        try {
            con.setAutoCommit(false);
            int i = 0 ;
            boolean isCommit = false;
            for ( ReceivedMsgInfo info : infos ) {
                long now = System.currentTimeMillis();
                String sql = "INSERT INTO `received_msgs_"+getCurrentTableName()+"`(`received_content`, `response_content`, " +
                        "`status`, `created_at`, `update_at`) VALUES (?,?,?,?,?)";
                pstmt=con.prepareStatement(sql);
                pstmt.setString(1, info.getReceivedContent());
                pstmt.setString(2, info.getResponseContent());
                pstmt.setInt(3, Constants.MSG_STATUS_NO_HANDLER);
                pstmt.setTimestamp(4, new java.sql.Timestamp(now));
                pstmt.setTimestamp(5, new java.sql.Timestamp(now));
                pstmt.addBatch();
                if (isCommit) {
                    isCommit = false;
                }
                if (i%10 ==0 ) {
                    pstmt.executeBatch();
                    con.commit();
                    isCommit = true;
                }
                i++;
            }
            if (!isCommit) {
                con.commit();
            }
            logger.debug("insertMsgLog "+infos.size());
        } catch(Exception e) {
            logger.info("insertMsgLog error "+e,e);
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeConn(pstmt,con);
        }
    }

    public void saveSendedMsgInfos(List<SendMsgInfo> infos) {
        Connection con = getConn();
        PreparedStatement pstmt = null;
        try {
            con.setAutoCommit(false);
            int i = 0 ;
            boolean isCommit = false;
            String sql = "INSERT INTO `send_msgs_"+getCurrentTableName()+"`(`sended_content`, `status`, " +
                    "`error_code`, `error_msg`, `created_at`, `update_at`) " +
                    "VALUES (?,?,?,?,?,?)";
            for ( SendMsgInfo info : infos ) {
                pstmt=con.prepareStatement(sql);
                pstmt.setString(1, info.getContent());
                pstmt.setInt(2, info.getStatus());
                pstmt.setInt(3, info.getError_code());
                pstmt.setString(4, info.getError_msg());
                pstmt.setTimestamp(5, new java.sql.Timestamp(System.currentTimeMillis()));
                pstmt.setTimestamp(6, new java.sql.Timestamp(System.currentTimeMillis()));
                pstmt.addBatch();
                if (isCommit) {
                    isCommit = false;
                }
                if (i%10 ==0 ) {
                    pstmt.executeBatch();
                    con.commit();
                    isCommit = true;
                }
                i++;
            }
            if (!isCommit) {
                con.commit();
            }
            logger.debug("update sendedMsg ");
        } catch(Exception e) {
            logger.info("update sendedMsg "+e,e);
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeConn(pstmt,con);
        }
    }

    public List<SendMsgInfo> getSendMsgInfos(int size) {
        String tableNameTail = getCurrentTableName();
        String sql = "select * from `send_msgs_"+tableNameTail+"` where status = "+Constants.MSG_STATUS_NO_HANDLER+
                " order by id desc limit 0,"+size;
        Connection con = getConn();
        Statement st = null;
        ResultSet rs = null;
        List<SendMsgInfo> infos = new ArrayList<SendMsgInfo>();
        try {
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                SendMsgInfo info = new SendMsgInfo();
                info.setId(rs.getLong("id"));
                info.setContent(rs.getString("sended_content"));
                info.setTableNameTail(tableNameTail);
                infos.add(info);
            }
        } catch(Exception e) {
            logger.info("get accessToken error "+e,e);
        } finally {
            closeConn(rs,st,con);
        }
        return infos;
    }

    public void updateSendedMsg(List<SendMsgInfo> infos) {
        Connection con = getConn();
        PreparedStatement pstmt = null;
        try {
            con.setAutoCommit(false);
            int i = 0 ;
            boolean isCommit = false;
            String sql = null;
            String lastTableNameTail = null;
            for ( SendMsgInfo info : infos ) {
                if (null == sql || null == lastTableNameTail) {
                    lastTableNameTail = info.getTableNameTail();
                    sql = "UPDATE `send_msgs_"+lastTableNameTail+"` SET `status`=?,`error_Code`=?,`error_Msg`=?,`update_at`=? WHERE `id`=?";
                } else if (!lastTableNameTail.equals(info.getTableNameTail())) {
                    sql = "UPDATE `send_msgs_"+lastTableNameTail+"` SET `status`=?,`error_Code`=?,`error_Msg`=?,`update_at`=? WHERE `id`=?";
                }
                pstmt=con.prepareStatement(sql);
                pstmt.setInt(1, info.getStatus());
                pstmt.setInt(2, info.getError_code());
                pstmt.setString(3, info.getError_msg());
                pstmt.setTimestamp(4, new java.sql.Timestamp(info.getSendTime()));
                pstmt.setLong(5,info.getId());
                pstmt.addBatch();
                if (isCommit) {
                    isCommit = false;
                }
                if (i%10 ==0 ) {
                    pstmt.executeBatch();
                    con.commit();
                    isCommit = true;
                }
                i++;
            }
            if (!isCommit) {
                con.commit();
            }
            logger.debug("update sendedMsg ");
        } catch(Exception e) {
            logger.info("update sendedMsg "+e,e);
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeConn(pstmt,con);
        }
    }


    public void createTable() {
        Calendar c = Calendar.getInstance();
        String receivedTableName = "received_msgs_"+formatter.format(c.getTime());
        checkReceivedTable(receivedTableName);
        String sendedTableName = "send_msgs_"+formatter.format(c.getTime());
        checkSendedTable(sendedTableName);

        c.add(Calendar.MONTH,1);
        receivedTableName = "received_msgs_"+formatter.format(c.getTime());
        checkReceivedTable(receivedTableName);
        sendedTableName = "send_msgs_"+formatter.format(c.getTime());
        checkSendedTable(sendedTableName);
    }

    private int checkTableExists(String tableName) {
        String checkSql = "SELECT COUNT(*)\n" +
                " FROM information_schema.tables\n" +
                " WHERE table_schema = '"+ Constants.DBNAME+"'\n" +
                " AND table_name = '"+tableName+"';";

        Connection con = getConn();
        Statement st = null;
        ResultSet rs = null;
        int count = 0 ;
        try {
            st = con.createStatement();
            rs = st.executeQuery(checkSql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch(Exception e) {
            logger.info("get table info error "+e,e);
        } finally {
            closeConn(rs,st,con);
        }
        return count;
    }

    private void checkReceivedTable(String tableName) {
        int count = checkTableExists(tableName);
        if (count == 0 ) {
            String sql = "CREATE TABLE IF NOT EXISTS `"+tableName+"` (\n" +
                    "  `id` bigint(20) NOT NULL,\n" +
                    "  `received_content` text COLLATE utf8_unicode_ci NOT NULL,\n" +
                    "  `response_content` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,\n" +
                    "  `status` int(11) NOT NULL,\n" +
                    "  `created_at` datetime DEFAULT NULL,\n" +
                    "  `update_at` datetime DEFAULT NULL\n" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" ;
            doExecSql(tableName, sql);
            doExecSql(tableName, " ALTER TABLE `"+tableName+"` ADD PRIMARY KEY (`id`) ");
            doExecSql(tableName, " ALTER TABLE `"+tableName+"` MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT");
        }
    }

    private void checkSendedTable(String tableName) {
        int count = checkTableExists(tableName);
        if (count == 0 ) {
            String sql = "CREATE TABLE IF NOT EXISTS `"+tableName+"` (\n" +
                    "  `id` bigint(20) NOT NULL,\n" +
                    "  `sended_content` text COLLATE utf8_unicode_ci NOT NULL,\n" +
                    "  `status` int(11) NOT NULL,\n" +
                    "  `error_code` int(11) NOT NULL,\n" +
                    "  `error_msg` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\n" +
                    "  `created_at` datetime DEFAULT NULL,\n" +
                    "  `update_at` datetime DEFAULT NULL\n" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" ;
            doExecSql(tableName, sql);
            doExecSql(tableName, " ALTER TABLE `"+tableName+"` ADD PRIMARY KEY (`id`) ");
            doExecSql(tableName, " ALTER TABLE `"+tableName+"` MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT");
        }

    }

    private void doExecSql(String tableName, String sql) {
        Connection con = getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt=con.prepareStatement(sql);
            pstmt.execute();
            logger.info("create table "+tableName);
        } catch(Exception e) {
            logger.info("create table "+tableName+ "error "+e,e);
        } finally {
            closeConn(pstmt,con);
        }
    }
}
